package fr.afcepf.atod18.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import fr.afcepf.atod18.data.Devise;

public class DaoDevise implements IdaoDevise {

	private final String reqAjout =
            "INSERT INTO devise (symbole, codeIso, libelle, taux) "
            + "VALUES (?,?,?,?)";
	public int ajouter(Devise paramDevise) {
		Connection cnx = UtilConnexionDevise.getConnexion();
		int cle=0;
        try {
            PreparedStatement pstmt =
                    cnx.prepareStatement(reqAjout,
                            Statement.RETURN_GENERATED_KEYS);
            pstmt.setString(1, paramDevise.getSymbole());
            pstmt.setString(2, paramDevise.getCodeIso());
            pstmt.setString(3, paramDevise.getLibelle());
            pstmt.setDouble(4, paramDevise.getTaux());
            int retour = pstmt.executeUpdate();
            if (retour == 1) {
                ResultSet rsKey = pstmt.getGeneratedKeys();
                if (rsKey.next()) {
                    cle = rsKey.getInt(1);
                }
            }
         } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                cnx.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return cle;
	}

	private final String reqUpdate =
            "UPDATE devise SET Symbole=?, codeIso =?, Libelle=?, Taux=? WHERE id=?";
	public void modifier(Devise paramDevise) {
		Connection cnx = UtilConnexionDevise.getConnexion();
        try {
            PreparedStatement pstmt =
                    cnx.prepareStatement(reqUpdate);

            pstmt.setString(1, paramDevise.getSymbole());
            pstmt.setString(2, paramDevise.getCodeIso());
            pstmt.setString(3, paramDevise.getLibelle());
            pstmt.setDouble(4, paramDevise.getTaux());
            pstmt.setInt(5, paramDevise.getId());
            pstmt.executeUpdate();
         } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                cnx.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
	}

	private final String reqDelete =
            "DELETE FROM devise WHERE id=?";
	public boolean supprimer(Devise paramDevise) {
		Connection cnx = UtilConnexionDevise.getConnexion();
        try {
            PreparedStatement pstmt =
                    cnx.prepareStatement(reqDelete);
            pstmt.setInt(1, paramDevise.getId());
            return  pstmt.executeUpdate() == 1;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                cnx.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
		return false;
	}

	private final String reqCherche =
            "SELECT * FROM devise WHERE codeIso=?";
	public Devise rechercher(String paramCodeIso) {
		Connection cnx = UtilConnexionDevise.getConnexion();
		
        try {
            PreparedStatement pstmt =
                    cnx.prepareStatement(reqCherche);
            pstmt.setString(1, paramCodeIso);
   		 	ResultSet rs = pstmt.executeQuery();
   		
   		 if(rs.next()){
   			Devise retour = new Devise();
   			 retour.setId(rs.getInt("id"));
	   		 retour.setSymbole(rs.getString("Symbole"));
	   		 retour.setCodeIso(rs.getString("codeIso"));
	   		 retour.setLibelle(rs.getString("Libelle"));
	   		 retour.setTaux(rs.getDouble("Taux"));
	   		 return retour;
   		 }
         } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                cnx.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
		 return null;
	}
	private final String reqListe =
            "SELECT * FROM devise";
	@Override
	public List<Devise> lister() {
        List<Devise> retour = new ArrayList();
        Connection cnx = UtilConnexionDevise.getConnexion();
        try {
           PreparedStatement pstmt =
                   cnx.prepareStatement(reqListe);
           ResultSet rs = pstmt.executeQuery();
           while (rs.next()) {
               Devise d = new Devise();
               d.setId(rs.getInt("id"));
               d.setSymbole(rs.getString("Symbole"));
               d.setCodeIso(rs.getString("codeIso"));
               d.setLibelle(rs.getString("libelle"));
               d.setTaux(rs.getDouble("Taux"));
               retour.add(d);
           }
       } catch (SQLException e) {
           e.printStackTrace();
       } finally {
           try {
               cnx.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
       }
        return retour;
	}

}
